package controllers;

import com.fasterxml.jackson.databind.JsonNode;
import com.google.gson.Gson;
import commons.Commons;
import entities.History;
import entities.Station;
import helpers.NiceJsObject;
import play.db.jpa.JPA;
import play.db.jpa.Transactional;
import play.libs.F;
import play.libs.Json;
import play.mvc.Controller;
import play.mvc.Result;

import javax.persistence.Query;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: eagami
 * Date: 03/05/14
 * Time: 16:02
 * To change this template use File | Settings | File Templates.
 */
public class StationsController extends Controller {

	//long to minute
	final long LTOM = 1000*60*60; 
	
    @Transactional
    public static Result saveStation(){
        JsonNode json = request().body().asJson();
        Gson gson = new Gson();
        Station station = null;
        if(json.findValue("name") != null && json.findValue("locationX") != null && json.findValue("locationY") != null){
            station = gson.fromJson(json.toString(), Station.class);
            station = station.saveStation();
        }
        else{
            return badRequest("invalid attribute exception");
        }
        System.out.println(json);
        return ok(Json.toJson(station));
    }

    @Transactional
    public static Result deleteStation(){
        JsonNode json = request().body().asJson();
        Gson gson = new Gson();
        Station station = null;
        if(json.findValue("id") != null && json.findValue("name") != null && json.findValue("locationX") != null && json.findValue("locationY") != null){
            station = gson.fromJson(json.toString(), Station.class);
            station.delete();
        }
        else{
            return badRequest("invalid attribute exception");
        }
        System.out.println(json);
        return ok();
    }

    @Transactional(readOnly = true)
    public static Result getAllStations(){
        return ok(Json.toJson(Station.getAllStations()));

    }

    @Transactional
    public static Result getQuantitativeGraph() throws ParseException {
        JsonNode json = request().body().asJson();
        Date startDate = null;
        Date endDate = null;
        List<Long> stations = null;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd/MM/yyyy, HH:mm");
        if(json.findValue("startDate") != null ){
            startDate = simpleDateFormat.parse(json.findValue("startDate").asText());
        }else{
            return badRequest();
        }

        if(json.findValue("endDate") != null){
            endDate = simpleDateFormat.parse(json.findValue("endDate").asText());
        }
        if(json.findValue("stations") != null && !json.findValue("stations").asText().equals("")){
            stations = Arrays.asList(Commons.stringArrayToLong(json.findValue("stations").asText().split(",")));
        }

        return ok(Json.toJson(getAmountOfObjectsInStationByDate(startDate, endDate, stations)));
    }

    @Transactional
    public static Result getAvgGraph() throws ParseException {
        JsonNode json = request().body().asJson();
        Date startDate = null;
        Date endDate = null;
        List<Long> stations = null;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd/MM/yyyy, HH:mm");
        if(json.findValue("startDate") != null ){
            startDate = simpleDateFormat.parse(json.findValue("startDate").asText());
        }else{
            return badRequest();
        }

        if(json.findValue("endDate") != null){
            endDate = simpleDateFormat.parse(json.findValue("endDate").asText());
        }
        if(json.findValue("stations") != null && !json.findValue("stations").asText().equals("")){
            stations = Arrays.asList(Commons.stringArrayToLong(json.findValue("stations").asText().split(",")));
        }

        return ok(Json.toJson(getAvgTimeForStation(startDate, endDate, stations)));
    }

    @Transactional
    public static Result getObjectHistory() throws ParseException {
        JsonNode json = request().body().asJson();
        Date startDate = null;
        Date endDate = null;
        List<Long> stations = null;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd/MM/yyyy, HH:mm");
        if(json.findValue("startDate") != null ){
            startDate = simpleDateFormat.parse(json.findValue("startDate").asText());
        }else{
            return badRequest();
        }

        if(json.findValue("endDate") != null){
            endDate = simpleDateFormat.parse(json.findValue("endDate").asText());
        }
        if(json.findValue("stations") != null && !json.findValue("stations").asText().equals("")){
            stations = Arrays.asList(Commons.stringArrayToLong(json.findValue("stations").asText().split(",")));
        }

        return ok(Json.toJson(getHistoryForStationByDate(startDate, endDate, stations)));
    }
    
    

    private static List<Object[]> getAmountOfObjectsInStationByDate(Date startDate, Date endDate,List<Long> stations){
        
        StringBuilder strQuery = new StringBuilder("select distinct s , count(s) ");
        						   strQuery.append("from History h ");
        						   strQuery.append("inner join h.station s ");
        						   strQuery.append("where h.startTime > ?1 ");
        
        if(stations != null && !stations.isEmpty()){
        	strQuery.append(" and s.id in (?2)");
        }
        						   
        if(endDate != null){
        	strQuery.append(" and h.lastUpdateTime < ?3 ");
        }
        
        strQuery.append("group by s ,s.locationX ,s.locationY ,s.name ");
        
        Query query = JPA.em().createQuery(strQuery.toString());
        
        query.setParameter(1, startDate);
        
        if(stations != null && !stations.isEmpty()){
        	query.setParameter(2, stations);
        }
        
        if(endDate != null){
        	query.setParameter(3, endDate);
        }

        return query.getResultList();
    }
    
    

    private static List<NiceJsObject<Station, Integer>> getAvgTimeForStation(Date startDate, Date endDate,List<Long> stations){
        
        StringBuilder strQuery = new StringBuilder("select distinct s ");
        						   strQuery.append("from History h ");
        						   strQuery.append("inner join h.station s ");
        						   strQuery.append("where h.startTime > ?1 ");
        
        if(stations != null && !stations.isEmpty()){
        	strQuery.append(" and s.id in (?2)");
        }
        						   
        if(endDate != null){
        	strQuery.append(" and h.lastUpdateTime < ?3 ");
        }
        
        Query query = JPA.em().createQuery(strQuery.toString());
        
        query.setParameter(1, startDate);
        
        if(stations != null && !stations.isEmpty()){
        	query.setParameter(2, stations);
        }
        
        if(endDate != null){
        	query.setParameter(3, endDate);
        }
        
        List<Station> lstQuery = query.getResultList();
        
       List<NiceJsObject<Station, Integer>> mReturn = new ArrayList<>();
        
        for (Station sta : lstQuery) {
			

			StringBuilder strAvgQuery = new StringBuilder("select AVG(DATEDIFF(MINUTE,start_time,h.Last_Update_Time)) ")
										.append("from History h ");
											
			
			if(sta != null)
				strAvgQuery.append(" where h.station_id = ?1 ");
			else  // why??
				strAvgQuery.append("where h.station_id is null");

			
			Query avgTimeQuery = JPA.em().createNativeQuery(strAvgQuery.toString());
	        
			if(sta != null)
				avgTimeQuery.setParameter(1, sta.getId());
			
			mReturn.add(new NiceJsObject<>(sta, Integer.parseInt(avgTimeQuery.getSingleResult().toString())));
		}

        return mReturn;
    }

    private static List<History> getHistoryForStationByDate(Date startDate, Date endDate,List<Long> stations){
        
        StringBuilder strQuery = new StringBuilder("select h ");
        						   strQuery.append("from History h ");
        						   strQuery.append("inner join h.station s ");
        						   strQuery.append("inner join h.object o ");
        						   strQuery.append("where h.startTime > ?1 ");
        
        if(stations != null && !stations.isEmpty()){
        	strQuery.append(" and s.id in (?2)");
        }
        						   
        if(endDate != null){
        	strQuery.append(" and h.lastUpdateTime < ?3 ");
        }
        
        Query query = JPA.em().createQuery(strQuery.toString());
        
        query.setParameter(1, startDate);
        
        if(stations != null && !stations.isEmpty()){
        	query.setParameter(2, stations);
        }
        
        if(endDate != null){
        	query.setParameter(3, endDate);
        }
        
        return query.getResultList();
        
        
    }
    
    public static Station getStationByLocation(final int x,final int y) throws Throwable{
    	
			return JPA.withTransaction(new F.Function0<Station>()  {
		        @Override
		        public Station apply() throws Throwable {
		        	int distatnce = 180;
		        	Station stations = null;
		        	StringBuilder strQuery = new StringBuilder("select distinct s ");
		    		   strQuery.append("from Station s ");
		    		   strQuery.append("where ?2 between locationX - ?1 and locationX + ?1 and  ");
		    		   strQuery.append("?3 between locationX - ?1 and locationX + ?1 ");
		    					   
		    			Query query = JPA.em().createQuery(strQuery.toString());
		    			
		    			query.setParameter(1, distatnce);
		    			query.setParameter(2, x);
		    			query.setParameter(3, y);
		    					
		    			List<Station> lReturn = query.getResultList();
		    			if(lReturn.isEmpty())
		    				return null;
	                return (Station) lReturn.get(0);
	             }
			});
			
    }
}	
